package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.proactive.webapp.dao.DesignCommentDAO;
import com.proactive.webapp.entity.DesignComment;
import com.proactive.webapp.entity.Member;
import com.proactive.webapp.util.DateConversion;

public class DesignCommentDAOImpl implements DesignCommentDAO{
	DateConversion dateConversion = new DateConversion();
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	@Override
	public List<DesignComment> getList(String loginAccount, int designID, int start, int size) {
		// TODO Auto-generated method stub
		List<DesignComment> designCommentList = new ArrayList<DesignComment>(); 
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment_report b WHERE a.designCommentID = b.designCommentID) "
				+ "AS reportCount, (SELECT COUNT(*) FROM design_comment_report c WHERE a.designCommentID = c.designCommentID AND c.memberAccount=?) "
				+ "AS report FROM design_comment a JOIN member d ON a.memberAccount = d.memberAccount "
				+ "WHERE designID = ? AND topCommentID IS NULL HAVING reportCount < 10 ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setInt(2, designID);
			smt.setInt(3, start);
			smt.setInt(4, size);
			rs = smt.executeQuery();
			while(rs.next()){
				DesignComment comment = new DesignComment();
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoPath(!rs.getString("memberPhoto").equals("")?rs.getString("memberPhoto"):"resources/image/default/userdefault.jpg");
				comment.setId(rs.getInt("designCommentID"));
				comment.setContent(rs.getString("designCommentContent"));
				comment.setAccount(rs.getString("memberAccount"));
				comment.setCreateDate(rs.getDate("createDate"));
				comment.setCreateDateString(dateConversion.ConversionYMDHM(rs.getTimestamp("createDate")));
				comment.setReportFlag(rs.getInt("report")==0?false:true);
				comment.setMember(member);
				designCommentList.add(comment);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return designCommentList;
	}

	@Override
	public List<DesignComment> getReplyList(String loginAccount, int commentID) {
		// TODO Auto-generated method stub
		List<DesignComment> replyCommentList = new ArrayList<DesignComment>(); 
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment_report b WHERE a.designCommentID = b.designCommentID) "
				+ "AS reportCount, (SELECT COUNT(*) FROM design_comment_report c WHERE a.designCommentID = c.designCommentID AND c.memberAccount=?) "
				+ "AS report FROM design_comment a JOIN member d ON a.memberAccount = d.memberAccount "
				+ "WHERE topCommentID = ? HAVING reportCount < 10 ORDER BY createDate DESC";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setInt(2, commentID);
			rs = smt.executeQuery();
			while(rs.next()){
				DesignComment comment = new DesignComment();
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoPath(!rs.getString("memberPhoto").equals("")?rs.getString("memberPhoto"):"resources/image/default/userdefault.jpg");
				comment.setId(rs.getInt("designCommentID"));
				comment.setContent(rs.getString("designCommentContent"));
				comment.setAccount(rs.getString("memberAccount"));
				comment.setCreateDate(rs.getDate("createDate"));
				comment.setCreateDateString(dateConversion.ConversionYMDHM(rs.getTimestamp("createDate")));
				comment.setReportFlag(rs.getInt("report")==0?false:true);
				comment.setMember(member);
				replyCommentList.add(comment);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return replyCommentList;
	}

	@Override
	public void insert(DesignComment comment) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO design_comment(designID, designCommentContent, "
				+ "memberAccount, createDate) VALUES(?,?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, comment.getDid());
			smt.setString(2, comment.getContent());
			smt.setString(3, comment.getAccount());
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public void delete(int commentID) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM design_comment WHERE designCommentID=? OR topCommentID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, commentID);
			smt.setInt(2, commentID);
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public int countTotal(int designID) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal,(SELECT COUNT(*) FROM design_comment_report b WHERE a.designCommentID = b.designCommentID) "
				+ "AS reportCount FROM design_comment a JOIN member c ON a.memberAccount = c.memberAccount "
				+ "WHERE designID = ? AND topCommentID IS NULL GROUP BY reportCount HAVING reportCount < 10 ORDER BY createDate DESC";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, designID);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public void insertReply(DesignComment comment) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO design_comment(designID, designCommentContent, memberAccount, topCommentID, "
				+ "createDate) VALUES(?,?,?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, comment.getDid());
			smt.setString(2, comment.getContent());
			smt.setString(3, comment.getAccount());
			smt.setInt(4, comment.getTopCommentID());
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public DesignComment get(DesignComment comment) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM design_comment WHERE designCommentID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, comment.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				comment.setId(rs.getInt("designCommentID"));
				comment.setContent(rs.getString("designCommentContent"));
				comment.setAccount(rs.getString("memberAccount"));
				comment.setCreateDate(rs.getDate("createDate"));
				comment.setCreateDateString(dateConversion.ConversionYMDHM(rs.getTimestamp("createDate")));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return comment;
	}
}
